﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SQLDocTool
{
    public partial class Form1 : Form
    {
       
        public  IniFileHelp ini = new IniFileHelp();

        public Dapper dapper = null;
        public Form1()
        {
            InitializeComponent();            
            txtConnectionString.Text = ini.GetString("connection", "connectionstrings", @"server=.;database=db;uid=sa;pwd=123456;");
        }
       

        private void Form1_Load(object sender, EventArgs e)
        {
          
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            dapper = new Dapper(txtConnectionString.Text.Trim());
            string msg = string.Empty;
            if (!dapper.TryConect(out msg))
            {
                labMsg.Text = msg;
                return;
            }
            ini.WriteValue("connection", "connectionstrings", txtConnectionString.Text.Trim());

            string sql = @"SELECT  Row_Number() over ( order by (case when a.colorder = 1 then d.name 
                   else '' end) asc)  as 序号, case when a.colorder = 1 then d.name 
                                   else '' end as 表名, 
                        case when a.colorder = 1 then isnull(f.value, '') 
                                     else '' end as 表说明
                FROM syscolumns a 
                       inner join sysobjects d 
                          on a.id = d.id 
                             and d.xtype = 'U' 
                             and d.name <> 'sys.extended_properties'
                       left join sys.extended_properties   f 
                         on a.id = f.major_id 
                            and f.minor_id = 0
                 where a.colorder = 1 and d.name<>'sysdiagrams'  order by 表名  asc";

            DataTable dt_TBNames = dapper.Query(sql).Tables[0];
            foreach (DataRow dr in dt_TBNames.Rows)
            {
                CboTb_Name.Items.Add(dr["表名"]);
            }
        }

        private void CboTb_Name_SelectedIndexChanged(object sender, EventArgs e)
        {           
            string tbName = CboTb_Name.Text;          
            txt_TabName_msdesc.Text = GetTableDesc(tbName);
           
            GV.DataSource = GetColumnDescWhere(tbName);
           
        }
        public  DataTable GetColumnDescWhere(string tableName)
        {
            string select_sql = @"SELECT A.name AS 表名 ,B.name AS 列名,C.value AS 字段说明 FROM sys.tables A INNER JOIN sys.columns B ON B.object_id = A.object_id LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id WHERE 1=1";
            if (tableName != null && !string.IsNullOrEmpty(tableName.Trim()))
            {
                select_sql += " and A.name = '" + tableName + "' ";
            }
            select_sql += " order by A.name asc ";
            return dapper.Query(select_sql).Tables[0];
        }

        public  string GetTableDesc(string tableName)
        {
            string select_sql = @"SELECT case when a.colorder = 1 then isnull(f.value, '')  else '' end as 表说明
FROM syscolumns a 
       inner join sysobjects d 
          on a.id = d.id 
             and d.xtype = 'U' 
             and d.name <> 'sys.extended_properties'
       left join sys.extended_properties   f 
         on a.id = f.major_id 
            and f.minor_id = 0
 where a.colorder = 1 and d.name<>'sysdiagrams'  and d.name='{0}' ";
            select_sql = string.Format(select_sql, tableName);
            object obj = dapper.GetSingle(select_sql);
            return obj == null ? string.Empty : obj.ToString();
        }
        public  string GetColumnDesc(string tableName, string columnName)
        {
            //该句sql只会显示有注释的所有表字段，没有则不会显示  select * from  ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default) 
            string result = string.Empty;
            string select_sql = "SELECT top 1 value FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default) where objname = '{1}'";
            select_sql = string.Format(select_sql, tableName, columnName);
            object obj = dapper.GetSingle(select_sql);
            return obj == null ? string.Empty : obj.ToString();
        }

        public  int UpsertColumnDesc(string tableName, string columnName, string ms_Description)
        {
            int res = 0;
            string upsert_sql = @"if exists (select * from   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default) where objname = '{1}') EXEC sp_updateextendedproperty   'MS_Description','{2}','user',dbo,'table','{0}','column',{1} else EXEC sp_addextendedproperty @name=N'MS_Description' , @value=N'{2}' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'{0}', @level2type=N'COLUMN', @level2name=N'{1}' ";
            upsert_sql = string.Format(upsert_sql, tableName, columnName, ms_Description);
            return dapper.ExecuteSql(upsert_sql);
        }
        public  int UpsertTableDesc(string tableName, string ms_Description)
        {
            string upset_sql = @" if exists (
                 SELECT case when a.colorder = 1 then d.name  else '' end as 表名,  case when a.colorder = 1 then isnull(f.value, '')  else '' end as 表说明
                FROM syscolumns a 
                       inner join sysobjects d 
                          on a.id = d.id 
                             and d.xtype = 'U' 
                             and d.name <> 'sys.extended_properties'
                       left join sys.extended_properties   f 
                         on a.id = f.major_id 
                            and f.minor_id = 0
                 where a.colorder = 1 and d.name<>'sysdiagrams'  and d.name='{0}' and f.value is not null
                 )
                 exec sp_updateextendedproperty N'MS_Description', N'{1}', N'user', N'dbo', N'table', N'{0}', NULL, NULL
                 else
                exec sp_addextendedproperty N'MS_Description', N'{1}', N'user', N'dbo', N'table', N'{0}', NULL, NULL";
            upset_sql = string.Format(upset_sql, tableName, ms_Description);
            return dapper.ExecuteSql(upset_sql);
        }

      
    }
}
